USE [loan]
GO
/****** Object:  UserDefinedFunction [dbo].[CalInterestBiMnthly]    Script Date: 04/25/2013 14:11:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CalInterestBiMnthly]
(
	@report_month INTEGER,
	@report_year INTEGER,
	@principal DECIMAL(18, 2),
	@interest_rate DECIMAL(18, 8),
	@loan_start_date DATETIME,	
	@term INTEGER,	
	@payment_amount DECIMAL(18, 2)	
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

DECLARE @interest DECIMAL(18, 8) = 0,	
	@payment_date DATETIME = DATEADD(wk, 2, @loan_start_date), -- 2 weeks after disbursement date
	@counter INTEGER = 1,
	@report_date DATETIME 
	
SET @report_date = dbo.DateFromParts --end of the the month for which the report is required
(
	@report_year, 
	@report_month, 
	DAY(DATEADD(mm, DATEDIFF(mm, -1, dbo.DateFromParts(@report_year, @report_month, 1)), -1))
)

SET @interest_rate = (@interest_rate / 100) / 26 --forthnightly interest rate

/*ensure that atleast one payment exist before the report date*/
IF @payment_date < @report_date
BEGIN
	/*loops through reducing the balance each forthnight from the start of the loan until the month before the report*/
	WHILE (YEAR(@payment_date) < @report_year OR (YEAR(@payment_date) = @report_year AND MONTH(@payment_date) < @report_month))	
	BEGIN
		IF @counter <= @term --ensure that the number of interest calculations does not exeed the number of terms
		BEGIN
			SET @interest = @principal * @interest_rate --calculate forthnightly interest
			SET @principal = @principal + @interest - @payment_amount --get new balance	
			SET @payment_date = DATEADD(wk, 2, @payment_date)	--set next payment date
			SET @counter = @counter + 1
		END	
		ELSE
		BEGIN
			BREAK
		END
	END	
	
	SET @interest = 0 --reset interest- disregarding interest for the months before the report
	
	/*Compute interest for the month of the report. The month of the payment date is the same as the month of the report*/
	WHILE @payment_date <= @report_date --ensure interest is only calculated for the report month
	BEGIN
		IF @counter <= @term--ensure that the number of interest calculations does not exeed the number of terms
		BEGIN
			SET @interest = @interest + (@principal * @interest_rate) --calculate forthnightly interest
			SET @principal = @principal + (@principal * @interest_rate) - @payment_amount --get new balance
			SET @payment_date = DATEADD(wk, 2, @payment_date) --set next payment date
			SET @counter = @counter + 1
		END
		ELSE
		BEGIN
			BREAK
		END	
	END
END
RETURN @interest --return the interest for the month
END;